package db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;

import config.SystemConfig;














import user.model.UserModel;
import user.view.LoginView;


public class UserValidate {
	public static boolean isValidUser(UserModel um)
	{
		String sqlString="select * from userinfo where id='"+um.getName()+"'and pwd='"+um.getPsw()+"'and type="+um.getType();
		Statement statement;
		try {
			statement = DataBaseManager.getConnection().createStatement();
		
		ResultSet rs= statement.executeQuery(sqlString);
		if(rs.next())
			return true;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public static boolean isExist(String username,int type)
	{
		
		String sqlString="select * from userinfo where id='"+username+"' and type="+type;
		Statement statement;
		try {
			statement = DataBaseManager.getConnection().createStatement();
		
		ResultSet rs= statement.executeQuery(sqlString);
		if(rs.next())
			return true;
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	public static int addUser(UserModel um)
	{
		String sql="insert into userinfo(id,pwd,type,address,phone) values('"+um.getName()+"','"+um.getPsw()+"',"+um.getType()+",'"+um.getAddress()+"','"+um.getPhone()+"')";
		Statement statement;
		int rs=0;
		try {
			statement = DataBaseManager.getConnection().createStatement();
		
		rs=statement.executeUpdate(sql);
		
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		
	}
	public static int updateInfo(UserModel um) {
		String sqlstr = "UPDATE userinfo SET pwd='"
				+ um.getPsw() + "',PHONE='"+um.getPhone()+"',ADDRESS='"+um.getAddress()+"' WHERE ID='" + um.getName() + "' and type="+um.getType();
		System.out.println(sqlstr);
		try {
			return DataBaseManager.getConnection().createStatement().executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static Vector<String> getUserInfo(String id,int type)
	{
		Vector<String> ret=new Vector<String>();
		String sql="select address,phone from userinfo where id='"+id+"' and type="+type;
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement().executeQuery(sql);
		
		
		if(rs.next())
		{
			ret.add(rs.getString(1));
			ret.add(rs.getString(2));
			
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return ret;
	}
	public static int deleteAccount(String name)
	{
		String sql = "delete  FROM userinfo where id='" + name + "'";

		try {
			return DataBaseManager.getConnection().createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static int insertManager(String name, String pwd) {
		String sqlstr = "insert into userinfo(id,pwd,type) VALUES ('"+ name + "','" + pwd + "',1)";

		try {
			return DataBaseManager.getConnection().createStatement().executeUpdate(sqlstr);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static int removeManagers(ArrayList<String> str) {
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("DELETE FROM userinfo WHERE MANAGER_ID IN (");
		for (String n : str) {
			sBuffer.append("'");
			sBuffer.append(n);
			sBuffer.append("',");

		}
		sBuffer.setLength(sBuffer.toString().length() - 1);
		sBuffer.append(")and type=1");
		System.out.println(sBuffer.toString());
		try {
			return DataBaseManager.getConnection().createStatement().executeUpdate(sBuffer.toString());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static boolean isOnline(String userid,int type)
	{
		String sql="SELECT * FROM sessions WHERE id='"+userid+"'AND type="+type +" limit 0,1";
		ResultSet rs;
		try {
			 rs = DataBaseManager.getConnection().createStatement().executeQuery(sql);
			if (rs.next()) 
				return true;
			
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return true;
	}
	public static int setExit()
	{
		if(SystemConfig.offline)return 1;
		String sql="delete from sessions where id='"+LoginView.getInstance().getUsername()+"' and type="+LoginView.getInstance().getUserType();
		try {
			System.out.println("exit");
			return DataBaseManager.getConnection().createStatement().executeUpdate(
					sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static int setLogin(String userid,int type)
	{
		Calendar calendar=Calendar.getInstance();
		
		String sql="insert into sessions VALUES ('"+userid+"',"+type+",'"+calendar.getTime().toString()+"')";
		try {
			
			int t= DataBaseManager.getConnection().createStatement().executeUpdate(sql);
			if(t==1)System.out.println("login");
			return t;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public static Vector<Vector<String>> getAccountOnline()
	{
		Vector<Vector<String>> ret=new Vector<Vector<String>>();
		String sql="select * from sessions";
		ResultSet rs;
		try {
			rs = DataBaseManager.getConnection().createStatement().executeQuery(sql);
		
		
		while(rs.next())
		{
			Vector<String> t=new Vector<String>();
			t.add(rs.getString(1));
			t.add(rs.getString(2));
			t.add(rs.getString(3));
			ret.add(t);
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return ret;
	}
	
	
}
